package com.rtx.test;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

import org.springframework.util.StringUtils;

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import com.rtx.edi.model.express.po.PlExpressResult;

@SuppressWarnings("all")
public class ExecuteSQL {
	
	//测试环境 123.60.2.77:13306 root yre1UAs8iFLO3*Mlv
	//正式环境 124.71.183.161:2395   root t6a*juQmXpQPew4LX3Y6BBbwQs5!8%vN
	
//	public static String host="123.60.2.77:13306";
//	public static String username="root";
//	public static String password="yre1UAs8iFLO3*Mlv";
	
	public static String host="l586544u1.oicp.vip:36106";
	public static String username="root";
	public static String password="yre1UAs8iFLO3*Mlv";
	
	public static void main(String[] args) throws Exception {
		List<String> whseids = Arrays.asList("ris_edi_test".split(","));
		List<Class> classList = Arrays.asList(PlExpressResult.class);
		for(Class clazz:classList) {
			for(String wmwhseid:whseids) {
				//获取实体类中新加的字段
				List<String> sqlList = queryDiffColumn(clazz,wmwhseid);
				for(String str:sqlList) {
					System.out.println(str);
				}
				//执行sql
				autoAddCoulmn(sqlList,wmwhseid);
			}
			
		}

	}
	
	
	public void ExecuteSQL() throws Exception {
		List<String> whseids = Arrays.asList("enterprise,wmwhse1,wmwhse2,wmwhse3,wmwhse4,wmwhse5,wmwhse6".split(","));
		List<String> sqlList = getSqlByFil("C:\\Users\\A\\git\\api\\edi-wms\\src\\main\\resources\\sql");
		
		for(String whseid:whseids) {
			Connection con = getconnection(whseid);
			Statement  statement  = con.createStatement();
			System.err.println("仓库"+whseid+"执行失败的sql");
			for(String sql:sqlList) {
				try {
					statement.execute(sql);
				} catch (Exception e) {
					System.err.println(sql);
				}
			}
			con.close();
		}
	}
	
	public static List<String>  queryColumnByTableName(String whseid,String tablename) throws Exception {
		List<String> columns = new ArrayList<String>();
		Connection conn = getconnection(whseid);
		Statement st = conn.createStatement();
		String sql = "show columns from "+tablename;
		ResultSet set = st.executeQuery(sql);
		while(set.next()) {
			columns.add(set.getString("Field").toLowerCase());
		}
		conn.close();
		return columns;
	}
	
	public static List<String> queryDiffColumn(Class clazz,String wmwhseid) throws Exception {
		List<String> diffSql = new ArrayList<String>();
		TableName TableName =	(com.baomidou.mybatisplus.annotation.TableName) clazz.getDeclaredAnnotation(TableName.class);
		List<String> columnList=queryColumnByTableName(wmwhseid,TableName.value());
		List<String> list  = columnList.stream().map(temp->camelCaseForMate(temp)).collect(Collectors.toList());
		for(Field field:clazz.getDeclaredFields()) {
			if(!list.contains(field.getName())) {
				TableName tableName = (TableName) clazz.getDeclaredAnnotation(TableName.class);
				TableField tableField = field.getDeclaredAnnotation(TableField.class);
				if(tableField !=null && false == tableField.exist()) {
					continue;
				}
				String sql = "alter table "+tableName.value()+" add " +field.getName()+" varchar(64);";
//				System.out.println();
				diffSql.add(sql);
			}
		}
		return diffSql;
	}
	
	public static void autoAddCoulmn(List<String> sqlList,String whseid) throws SQLException {
		Connection conn =  getconnection(whseid);
		Statement  statement  = conn.createStatement();
		for(String sql:sqlList) {
			try {
				statement.execute(sql);
				System.out.println("执行成功sql-"+sql);
			} catch (Exception e) {
				System.out.println("执行失败sql-"+sql);
				System.out.println("执行失败原因"+e.getMessage());
			}
		
		}
		conn.close();
	}

	//测试环境 123.60.2.77:13306 root yre1UAs8iFLO3*Mlv
	//正式环境 124.71.183.161:2395   root t6a*juQmXpQPew4LX3Y6BBbwQs5!8%vN
	public static Connection getconnection(String whseid){
		Connection con = null;
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			String url ="jdbc:mysql://"+host+"/"+whseid+"?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF8&autoReconnect=true&rewriteBatchedStatements=true&allowMultiQueries=true";
			con = DriverManager.getConnection(url, username, password);
			
		
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return con;
	}
	
	public static  List<String> getSqlByFil(String path) throws Exception {
		List<String> sql = new ArrayList<>();
		File file = new File(path);
		if(file.isDirectory()) {
			for(File sqlFile:file.listFiles()) {
				
				boolean isALL = false;
				if(sqlFile.getName().endsWith("sql")) {
					isALL = true;
				}else {
					isALL = false;
				}
				
				BufferedReader reader = new BufferedReader(new FileReader(sqlFile));
				StringBuffer allText = new StringBuffer();
				while(reader.ready()) {
					String line = reader.readLine();
					if(StringUtils.hasLength(line)) {
						if(isALL) {
							allText.append(line);
						}else {
							sql.add(line);
						}
					}
				}
				
				if(isALL) {
					sql.add(allText.toString());
				}
				
				reader.close();
			}
			
		}else {
			
			BufferedReader reader = new BufferedReader(new FileReader(path));
			while(reader.ready()) {
				sql.add(reader.readLine());
			}
			reader.close();
			
		}
		

		return sql;
	}
	
    public static String xX2x_x(String str) {
        Pattern compile = Pattern.compile("[A-Z]");
        Matcher matcher = compile.matcher(str);
        StringBuffer sb = new StringBuffer();
        while(matcher.find()) {
            matcher.appendReplacement(sb,  "_" + matcher.group(0).toLowerCase());
        }
        matcher.appendTail(sb);
        return sb.toString();
    }
    
	public static String camelCaseForMate(String str) {
		List<String> list1 = Arrays.asList(str.split("_"));
		StringBuffer sb = new StringBuffer();
		for(String s:list1) {
			if(list1.indexOf(s)!=0) {
				sb.append(s.substring(0, 1).toUpperCase()+s.substring(1));
			}else{
				sb.append(s);
			}
		}
		return sb.toString();
	}
}
